﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Xml;
using System.Xml.Xsl;
using System.IO;
using System.Text;
using System.Net.Mail;
using Microsoft.SqlServer.Server;

    public partial class UserDefinedFunctions
    {
        /// <summary>
        /// This function exports an query to a csv file
        /// </summary>
        /// <param name="query">Query for export</param>
        /// <param name="fileName">full path and filename for csv file</param>
        /// <returns></returns>
        [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
        public static SqlInt64 ExportToCSV(SqlString query, SqlString fileName)
        {
            int rows = 0;

            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(query.Value, conn))
                {
                    StringBuilder sb = new StringBuilder();
                    bool headerWritten = false;
                    char textQualifier = '"';


                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        rows = dr.RecordsAffected;

                        while (dr.Read())
                        {
                            if (!headerWritten)
                            {
                                for (int i = 0; i < dr.FieldCount; i++)
                                {
                                    if (typeof(System.String).Equals(dr.GetFieldType(i)))
                                    {
                                        sb.Append(textQualifier);
                                        if (i == 0)
                                            sb.Append("ZEITRAUM");
                                        else

                                            sb.Append(dr.GetName(i));

                                        sb.Append(textQualifier);
                                        sb.Append(";");
                                    }
                                    else
                                    {
                                        sb.Append(dr.GetName(i));
                                        sb.Append(";");
                                    }
                                }
                                sb.Append("\n");
                                headerWritten = true;
                            }

                            if (headerWritten)
                            {
                                for (int i = 0; i < dr.FieldCount; i++)
                                {
                                    if (typeof(System.String).Equals(dr.GetFieldType(i)))
                                    {
                                        sb.Append("=");
                                        sb.Append(textQualifier);
                                        sb.Append(dr[i].ToString());
                                        sb.Append(textQualifier);
                                        sb.Append(";");
                                    }
                                    else if (typeof(System.DateTime).Equals(dr.GetFieldType(i)))
                                    {
                                        sb.Append(String.Format("{0:dd.MM.yyyy}", dr[i]));
                                        sb.Append(";");
                                    }
                                    else
                                    {
                                        sb.Append(dr[i].ToString());
                                        sb.Append(";");
                                    }

                                }
                            }
                            sb.Append("\n");
                        }
                    }
                    conn.Close();
                    File.WriteAllText(fileName.Value, sb.ToString(), Encoding.Default);
                }
            }

            return rows;
        }
}
